"""
完整的数据库系统测试用例
包含从CREATE DATABASE到权限管理的完整流程
"""

def get_complete_test_cases():
    """获取完整的数据库系统测试用例"""
    return [
        # ====================================================================
        # 第一部分：数据库和表的基础操作
        # ====================================================================
        
        # 1. 创建数据库
        {
            "name": "01-创建公司数据库",
            "sql": "CREATE DATABASE company_db1;",
            "description": "第一部分：数据库和表的基础操作 - 创建公司数据库"
        },
        
        # 2. 使用数据库
        {
            "name": "02-使用公司数据库",
            "sql": "USE company_db1;",
            "description": "切换到新创建的公司数据库"
        },
        
        # 3. 查看当前数据库列表
        {
            "name": "03-查看数据库列表",
            "sql": "LIST DATABASES;",
            "description": "查看系统中现有的数据库"
        },
        {
            "name": "04-显示数据库列表",
            "sql": "SHOW DATABASES;",
            "description": "使用SHOW命令显示数据库列表"
        },
        
        # ====================================================================
        # 第二部分：创建基础表结构
        # ====================================================================
        
        # 4. 创建部门表
        {
            "name": "05-创建部门表",
            "sql": "CREATE TABLE departments(dept_id INT, dept_name VARCHAR, location VARCHAR, budget INT);",
            "description": "第二部分：创建基础表结构 - 创建部门表"
        },
        
        # 5. 创建员工表
        {
            "name": "06-创建员工表",
            "sql": "CREATE TABLE employees(emp_id INT, emp_name VARCHAR, dept_id INT, salary INT, hire_date VARCHAR, email VARCHAR);",
            "description": "创建员工表"
        },
        
        # 6. 创建项目表
        {
            "name": "07-创建项目表",
            "sql": "CREATE TABLE projects(project_id INT, project_name VARCHAR, dept_id INT, start_date VARCHAR, end_date VARCHAR, budget INT);",
            "description": "创建项目表"
        },
        
        # 7. 创建客户表
        {
            "name": "08-创建客户表",
            "sql": "CREATE TABLE customers(customer_id INT, customer_name VARCHAR, contact_email VARCHAR, phone VARCHAR, address VARCHAR);",
            "description": "创建客户表"
        },
        
        # ====================================================================
        # 第三部分：插入测试数据
        # ====================================================================
        
        # 8. 插入部门数据
        {
            "name": "09-插入部门数据1",
            "sql": "INSERT INTO departments(dept_id, dept_name, location, budget) VALUES(1, 'Engineering', 'Building A', 1000000);",
            "description": "第三部分：插入测试数据 - 插入工程部数据"
        },
        {
            "name": "10-插入部门数据2",
            "sql": "INSERT INTO departments(dept_id, dept_name, location, budget) VALUES(2, 'Marketing', 'Building B', 500000);",
            "description": "插入市场部数据"
        },
        {
            "name": "11-插入部门数据3",
            "sql": "INSERT INTO departments(dept_id, dept_name, location, budget) VALUES(3, 'Sales', 'Building C', 750000);",
            "description": "插入销售部数据"
        },
        {
            "name": "12-插入部门数据4",
            "sql": "INSERT INTO departments(dept_id, dept_name, location, budget) VALUES(4, 'HR', 'Building A', 300000);",
            "description": "插入人力资源部数据"
        },
        {
            "name": "13-插入部门数据5",
            "sql": "INSERT INTO departments(dept_id, dept_name, location, budget) VALUES(5, 'Finance', 'Building B', 400000);",
            "description": "插入财务部数据"
        },
        
        # 9. 插入员工数据
        {
            "name": "14-插入员工数据1",
            "sql": "INSERT INTO employees(emp_id, emp_name, dept_id, salary, hire_date, email) VALUES(101, 'Alice Johnson', 1, 85000, '2023-01-15', 'alice.j@company.com');",
            "description": "插入员工Alice Johnson"
        },
        {
            "name": "15-插入员工数据2",
            "sql": "INSERT INTO employees(emp_id, emp_name, dept_id, salary, hire_date, email) VALUES(102, 'Bob Smith', 1, 78000, '2023-02-20', 'bob.s@company.com');",
            "description": "插入员工Bob Smith"
        },
        {
            "name": "16-插入员工数据3",
            "sql": "INSERT INTO employees(emp_id, emp_name, dept_id, salary, hire_date, email) VALUES(103, 'Carol Davis', 2, 65000, '2023-03-10', 'carol.d@company.com');",
            "description": "插入员工Carol Davis"
        },
        {
            "name": "17-插入员工数据4",
            "sql": "INSERT INTO employees(emp_id, emp_name, dept_id, salary, hire_date, email) VALUES(104, 'David Wilson', 2, 70000, '2023-01-25', 'david.w@company.com');",
            "description": "插入员工David Wilson"
        },
        {
            "name": "18-插入员工数据5",
            "sql": "INSERT INTO employees(emp_id, emp_name, dept_id, salary, hire_date, email) VALUES(105, 'Eva Brown', 3, 72000, '2023-04-05', 'eva.b@company.com');",
            "description": "插入员工Eva Brown"
        },
        {
            "name": "19-插入员工数据6",
            "sql": "INSERT INTO employees(emp_id, emp_name, dept_id, salary, hire_date, email) VALUES(106, 'Frank Miller', 3, 68000, '2023-02-15', 'frank.m@company.com');",
            "description": "插入员工Frank Miller"
        },
        {
            "name": "20-插入员工数据7",
            "sql": "INSERT INTO employees(emp_id, emp_name, dept_id, salary, hire_date, email) VALUES(107, 'Grace Lee', 4, 55000, '2023-03-20', 'grace.l@company.com');",
            "description": "插入员工Grace Lee"
        },
        {
            "name": "21-插入员工数据8",
            "sql": "INSERT INTO employees(emp_id, emp_name, dept_id, salary, hire_date, email) VALUES(108, 'Henry Taylor', 5, 80000, '2023-01-10', 'henry.t@company.com');",
            "description": "插入员工Henry Taylor"
        },
        
        # 10. 插入项目数据
        {
            "name": "22-插入项目数据1",
            "sql": "INSERT INTO projects(project_id, project_name, dept_id, start_date, end_date, budget) VALUES(201, 'Mobile App Development', 1, '2023-01-01', '2023-12-31', 500000);",
            "description": "插入移动应用开发项目"
        },
        {
            "name": "23-插入项目数据2",
            "sql": "INSERT INTO projects(project_id, project_name, dept_id, start_date, end_date, budget) VALUES(202, 'Website Redesign', 1, '2023-03-01', '2023-09-30', 200000);",
            "description": "插入网站重设计项目"
        },
        {
            "name": "24-插入项目数据3",
            "sql": "INSERT INTO projects(project_id, project_name, dept_id, start_date, end_date, budget) VALUES(203, 'Marketing Campaign Q2', 2, '2023-04-01', '2023-06-30', 150000);",
            "description": "插入第二季度营销活动项目"
        },
        {
            "name": "25-插入项目数据4",
            "sql": "INSERT INTO projects(project_id, project_name, dept_id, start_date, end_date, budget) VALUES(204, 'Sales Training Program', 3, '2023-02-01', '2023-05-31', 100000);",
            "description": "插入销售培训项目"
        },
        {
            "name": "26-插入项目数据5",
            "sql": "INSERT INTO projects(project_id, project_name, dept_id, start_date, end_date, budget) VALUES(205, 'HR System Upgrade', 4, '2023-05-01', '2023-11-30', 80000);",
            "description": "插入人力资源系统升级项目"
        },
        
        # 11. 插入客户数据
        {
            "name": "27-插入客户数据1",
            "sql": "INSERT INTO customers(customer_id, customer_name, contact_email, phone, address) VALUES(301, 'TechCorp Inc', 'contact@techcorp.com', '555-0101', '123 Tech Street');",
            "description": "插入客户TechCorp Inc"
        },
        {
            "name": "28-插入客户数据2",
            "sql": "INSERT INTO customers(customer_id, customer_name, contact_email, phone, address) VALUES(302, 'Global Solutions', 'info@globalsol.com', '555-0102', '456 Business Ave');",
            "description": "插入客户Global Solutions"
        },
        {
            "name": "29-插入客户数据3",
            "sql": "INSERT INTO customers(customer_id, customer_name, contact_email, phone, address) VALUES(303, 'StartUp Ventures', 'hello@startup.com', '555-0103', '789 Innovation Blvd');",
            "description": "插入客户StartUp Ventures"
        },
        {
            "name": "30-插入客户数据4",
            "sql": "INSERT INTO customers(customer_id, customer_name, contact_email, phone, address) VALUES(304, 'Enterprise Systems', 'support@enterprise.com', '555-0104', '321 Corporate Way');",
            "description": "插入客户Enterprise Systems"
        },
        {
            "name": "31-插入客户数据5",
            "sql": "INSERT INTO customers(customer_id, customer_name, contact_email, phone, address) VALUES(305, 'Digital Dynamics', 'sales@digitaldyn.com', '555-0105', '654 Modern Plaza');",
            "description": "插入客户Digital Dynamics"
        },
        
        # ====================================================================
        # 第四部分：基础查询操作
        # ====================================================================
        
        # 12. 查看所有表的数据
        {
            "name": "32-查看部门表数据",
            "sql": "SELECT * FROM departments;",
            "description": "第四部分：基础查询操作 - 查看所有部门数据"
        },
        {
            "name": "33-查看员工表数据",
            "sql": "SELECT * FROM employees;",
            "description": "查看所有员工数据"
        },
        {
            "name": "34-查看项目表数据",
            "sql": "SELECT * FROM projects;",
            "description": "查看所有项目数据"
        },
        {
            "name": "35-查看客户表数据",
            "sql": "SELECT * FROM customers;",
            "description": "查看所有客户数据"
        },
        
        # 13. 条件查询
        {
            "name": "36-高薪员工查询",
            "sql": "SELECT * FROM employees WHERE salary > 70000;",
            "description": "查询薪资超过70000的员工"
        },
        {
            "name": "37-高预算部门查询",
            "sql": "SELECT * FROM departments WHERE budget > 500000;",
            "description": "查询预算超过500000的部门"
        },
        {
            "name": "38-工程部项目查询",
            "sql": "SELECT * FROM projects WHERE dept_id = 1;",
            "description": "查询工程部的所有项目"
        },
        
        # 14. 排序查询
        {
            "name": "39-员工薪资降序排序",
            "sql": "SELECT * FROM employees ORDER BY salary DESC;",
            "description": "按薪资降序排列员工"
        },
        {
            "name": "40-部门名称升序排序",
            "sql": "SELECT * FROM departments ORDER BY dept_name ASC;",
            "description": "按部门名称升序排列"
        },
        {
            "name": "41-项目预算降序排序",
            "sql": "SELECT * FROM projects ORDER BY budget DESC;",
            "description": "按项目预算降序排列"
        },
        
        # 15. 限制结果数量
        {
            "name": "42-前3名高薪员工",
            "sql": "SELECT * FROM employees ORDER BY salary DESC LIMIT 3;",
            "description": "查询前3名薪资最高的员工"
        },
        {
            "name": "43-前2名高预算部门",
            "sql": "SELECT * FROM departments ORDER BY budget DESC LIMIT 2;",
            "description": "查询前2名预算最高的部门"
        },
        
        # ====================================================================
        # 第五部分：高级查询操作
        # ====================================================================
        
        # 16. 聚合函数查询
        {
            "name": "44-员工总数统计",
            "sql": "SELECT COUNT(*) FROM employees;",
            "description": "第五部分：高级查询操作 - 统计员工总数"
        },
        {
            "name": "45-员工薪资总和",
            "sql": "SELECT SUM(salary) FROM employees;",
            "description": "计算所有员工薪资总和"
        },
        {
            "name": "46-员工平均薪资",
            "sql": "SELECT AVG(salary) FROM employees;",
            "description": "计算员工平均薪资"
        },
        {
            "name": "47-最高和最低薪资",
            "sql": "SELECT MAX(salary), MIN(salary) FROM employees;",
            "description": "查找最高和最低薪资"
        },
        {
            "name": "48-部门统计信息",
            "sql": "SELECT COUNT(*), SUM(budget), AVG(budget) FROM departments;",
            "description": "部门数量、预算总和和平均预算"
        },
        {
            "name": "49-项目统计信息",
            "sql": "SELECT COUNT(*), SUM(budget) FROM projects;",
            "description": "项目数量和预算总和"
        },
        
        # 17. 分组查询
        {
            "name": "50-按部门统计员工数",
            "sql": "SELECT dept_id, COUNT(*) FROM employees GROUP BY dept_id;",
            "description": "按部门分组统计员工数量"
        },
        {
            "name": "51-按部门统计薪资总和",
            "sql": "SELECT dept_id, SUM(salary) FROM employees GROUP BY dept_id;",
            "description": "按部门分组统计薪资总和"
        },
        {
            "name": "52-按部门统计平均薪资",
            "sql": "SELECT dept_id, AVG(salary) FROM employees GROUP BY dept_id;",
            "description": "按部门分组统计平均薪资"
        },
        
        # 18. 复合条件查询
        {
            "name": "53-工程部高薪员工",
            "sql": "SELECT * FROM employees WHERE salary > 70000 AND dept_id = 1;",
            "description": "查询工程部薪资超过70000的员工"
        },
        {
            "name": "54-薪资范围查询",
            "sql": "SELECT * FROM employees WHERE salary < 60000 OR salary > 80000;",
            "description": "查询薪资低于60000或高于80000的员工"
        },
        {
            "name": "55-员工姓名模糊查询",
            "sql": "SELECT * FROM employees WHERE emp_name LIKE 'A%';",
            "description": "查询姓名以A开头的员工"
        },
        
        # 19. JOIN操作
        {
            "name": "56-员工部门内连接",
            "sql": "SELECT e.emp_name, d.dept_name FROM employees e INNER JOIN departments d ON e.dept_id = d.dept_id;",
            "description": "员工和部门表的内连接"
        },
        {
            "name": "57-员工部门左连接",
            "sql": "SELECT e.emp_name, d.dept_name FROM employees e LEFT JOIN departments d ON e.dept_id = d.dept_id;",
            "description": "员工和部门表的左连接"
        },
        {
            "name": "58-员工部门右连接",
            "sql": "SELECT e.emp_name, d.dept_name FROM employees e RIGHT JOIN departments d ON e.dept_id = d.dept_id;",
            "description": "员工和部门表的右连接"
        },
        
        # ====================================================================
        # 第六部分：数据更新操作
        # ====================================================================
        
        # 20. 更新员工薪资
        {
            "name": "59-更新员工薪资",
            "sql": "UPDATE employees SET salary = 90000 WHERE emp_id = 101;",
            "description": "第六部分：数据更新操作 - 更新Alice Johnson的薪资"
        },
        {
            "name": "60-批量加薪",
            "sql": "UPDATE employees SET salary = salary + 5000 WHERE dept_id = 1;",
            "description": "给工程部所有员工加薪5000"
        },
        
        # 21. 更新部门预算
        {
            "name": "61-更新部门预算",
            "sql": "UPDATE departments SET budget = 1200000 WHERE dept_id = 1;",
            "description": "更新工程部预算"
        },
        {
            "name": "62-更新部门位置",
            "sql": "UPDATE departments SET location = 'Building D' WHERE dept_id = 2;",
            "description": "更新市场部位置"
        },
        
        # 22. 更新项目信息
        {
            "name": "63-更新项目预算",
            "sql": "UPDATE projects SET budget = 250000 WHERE project_id = 202;",
            "description": "更新网站重设计项目预算"
        },
        {
            "name": "64-更新项目结束日期",
            "sql": "UPDATE projects SET end_date = '2023-10-31' WHERE project_id = 202;",
            "description": "更新网站重设计项目结束日期"
        },
        
        # 23. 验证更新结果
        {
            "name": "65-验证员工更新",
            "sql": "SELECT * FROM employees WHERE emp_id = 101;",
            "description": "验证Alice Johnson的薪资更新"
        },
        {
            "name": "66-验证工程部员工",
            "sql": "SELECT * FROM employees WHERE dept_id = 1;",
            "description": "验证工程部员工薪资更新"
        },
        {
            "name": "67-验证部门更新",
            "sql": "SELECT * FROM departments WHERE dept_id IN (1, 2);",
            "description": "验证部门信息更新"
        },
        {
            "name": "68-验证项目更新",
            "sql": "SELECT * FROM projects WHERE project_id = 202;",
            "description": "验证项目信息更新"
        },
        
        # ====================================================================
        # 第七部分：数据删除操作
        # ====================================================================
        
        # 24. 删除特定记录
        {
            "name": "69-删除特定客户",
            "sql": "DELETE FROM customers WHERE customer_id = 305;",
            "description": "第七部分：数据删除操作 - 删除Digital Dynamics客户"
        },
        {
            "name": "70-删除特定项目",
            "sql": "DELETE FROM projects WHERE project_id = 205;",
            "description": "删除HR系统升级项目"
        },
        
        # 25. 条件删除
        {
            "name": "71-删除低薪员工",
            "sql": "DELETE FROM employees WHERE salary < 60000;",
            "description": "删除薪资低于60000的员工"
        },
        
        # 26. 验证删除结果
        {
            "name": "72-验证客户删除",
            "sql": "SELECT * FROM customers;",
            "description": "验证客户删除结果"
        },
        {
            "name": "73-验证项目删除",
            "sql": "SELECT * FROM projects;",
            "description": "验证项目删除结果"
        },
        {
            "name": "74-验证员工删除",
            "sql": "SELECT * FROM employees;",
            "description": "验证员工删除结果"
        },
        
        # ====================================================================
        # 第八部分：用户管理和权限测试
        # ====================================================================
        
        # 27. 创建测试用户
        {
            "name": "75-创建用户alice",
            "sql": "CREATE USER alice IDENTIFIED BY 'alice123';",
            "description": "第八部分：用户管理和权限测试 - 创建用户alice"
        },
        {
            "name": "76-创建用户bob",
            "sql": "CREATE USER bob IDENTIFIED BY 'bob123';",
            "description": "创建用户bob"
        },
        {
            "name": "77-创建用户manager",
            "sql": "CREATE USER manager IDENTIFIED BY 'manager123';",
            "description": "创建用户manager"
        },
        {
            "name": "78-创建用户analyst",
            "sql": "CREATE USER analyst IDENTIFIED BY 'analyst123';",
            "description": "创建用户analyst"
        },
        
        # ====================================================================
        # 第九部分：权限授予测试
        # ====================================================================
        
        # 29. 授予基础查询权限
        {
            "name": "79-授予alice查询权限",
            "sql": "GRANT SELECT ON employees TO alice;",
            "description": "第九部分：权限授予测试 - 授予alice员工表查询权限"
        },
        {
            "name": "80-授予alice部门查询权限",
            "sql": "GRANT SELECT ON departments TO alice;",
            "description": "授予alice部门表查询权限"
        },
        
        # 30. 授予数据修改权限
        {
            "name": "81-授予bob插入权限",
            "sql": "GRANT INSERT ON employees TO bob;",
            "description": "授予bob员工表插入权限"
        },
        {
            "name": "82-授予bob更新权限",
            "sql": "GRANT UPDATE ON employees TO bob;",
            "description": "授予bob员工表更新权限"
        },
        {
            "name": "83-授予bob删除权限",
            "sql": "GRANT DELETE ON employees TO bob;",
            "description": "授予bob员工表删除权限"
        },
        
        # 31. 授予表管理权限
        {
            "name": "84-授予manager创建权限",
            "sql": "GRANT CREATE ON company_db1 TO manager;",
            "description": "授予manager数据库创建权限"
        },
        {
            "name": "85-授予manager删除权限",
            "sql": "GRANT DROP ON company_db1 TO manager;",
            "description": "授予manager数据库删除权限"
        },
        
        # 32. 授予分析师权限（只读权限）
        {
            "name": "86-授予analyst员工查询权限",
            "sql": "GRANT SELECT ON employees TO analyst;",
            "description": "授予analyst员工表查询权限"
        },
        {
            "name": "87-授予analyst部门查询权限",
            "sql": "GRANT SELECT ON departments TO analyst;",
            "description": "授予analyst部门表查询权限"
        },
        {
            "name": "88-授予analyst项目查询权限",
            "sql": "GRANT SELECT ON projects TO analyst;",
            "description": "授予analyst项目表查询权限"
        },
        {
            "name": "89-授予analyst客户查询权限",
            "sql": "GRANT SELECT ON customers TO analyst;",
            "description": "授予analyst客户表查询权限"
        },
        
        # 33. 授予管理员全部权限
        {
            "name": "90-授予manager全部权限",
            "sql": "GRANT ALL ON company_db1 TO manager;",
            "description": "授予manager数据库全部权限"
        },
        
        # ====================================================================
        # 第十部分：复杂查询和数据分析
        # ====================================================================
        
        # 40. 部门统计分析
        {
            "name": "91-部门员工统计",
            "sql": "SELECT dept_id, COUNT(*) as employee_count, SUM(salary) as total_salary, AVG(salary) as avg_salary FROM employees GROUP BY dept_id;",
            "description": "第十部分：复杂查询和数据分析 - 按部门统计员工数量和薪资"
        },
        
        # 43. 项目预算统计
        {
            "name": "92-项目预算统计",
            "sql": "SELECT dept_id, COUNT(*) as project_count, SUM(budget) as total_project_budget FROM projects GROUP BY dept_id;",
            "description": "按部门统计项目数量和预算"
        },
        
        # ====================================================================
        # 第十一部分：数据完整性测试
        # ====================================================================
        
        # 44. 测试边界条件
        {
            "name": "93-查询零薪资员工",
            "sql": "SELECT * FROM employees WHERE salary = 0;",
            "description": "第十一部分：数据完整性测试 - 查询薪资为0的员工"
        },
        {
            "name": "94-查询负预算部门",
            "sql": "SELECT * FROM departments WHERE budget < 0;",
            "description": "查询预算为负数的部门"
        },
        
        # 45. 测试数据类型
        {
            "name": "95-插入测试员工",
            "sql": "INSERT INTO employees(emp_id, emp_name, dept_id, salary, hire_date, email) VALUES(999, 'Test Employee', 1, 50000, '2023-06-15', 'test999@company.com');",
            "description": "插入测试员工数据"
        },
        {
            "name": "96-查询测试员工",
            "sql": "SELECT * FROM employees WHERE emp_id = 999;",
            "description": "查询测试员工"
        },
        {
            "name": "97-删除测试员工",
            "sql": "DELETE FROM employees WHERE emp_id = 999;",
            "description": "删除测试员工"
        },
        
        # ====================================================================
        # 第十二部分：清理和总结
        # ====================================================================
        
        # 47. 删除测试用户（清理）
        {
            "name": "98-删除用户alice",
            "sql": "DROP USER alice;",
            "description": "第十二部分：清理和总结 - 删除用户alice"
        },
        {
            "name": "99-删除用户bob",
            "sql": "DROP USER bob;",
            "description": "删除用户bob"
        },
        {
            "name": "100-删除用户manager",
            "sql": "DROP USER manager;",
            "description": "删除用户manager"
        },
        {
            "name": "101-删除用户analyst",
            "sql": "DROP USER analyst;",
            "description": "删除用户analyst"
        },
        
        # 48. 最终数据验证
        {
            "name": "102-最终员工数据",
            "sql": "SELECT * FROM employees ORDER BY emp_id;",
            "description": "最终验证员工数据"
        },
        {
            "name": "103-最终部门数据",
            "sql": "SELECT * FROM departments ORDER BY dept_id;",
            "description": "最终验证部门数据"
        },
        {
            "name": "104-最终项目数据",
            "sql": "SELECT * FROM projects ORDER BY project_id;",
            "description": "最终验证项目数据"
        },
        {
            "name": "105-最终客户数据",
            "sql": "SELECT * FROM customers ORDER BY customer_id;",
            "description": "最终验证客户数据"
        }
    ]
